package com.xxx.DAO;

import com.sun.org.apache.xpath.internal.operations.Bool;
import com.xxx.entity.Book;
import com.xxx.util.JDBCUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class booksDao {

    private JDBCUtil util = new JDBCUtil();
    //添加一行数据
    public int add(String bName,String bAut,String bPub,int bSum,String path)  {
        String sql = "insert into books (bName,bAut,bPub,bSum,path) values(?,?,?,?,?)";
        int result = 0;
        PreparedStatement ps = util.createPstmt(sql);
        try {
            ps.setString(1, bName);
            ps.setString(2, bAut);
            ps.setString(3,bPub);
            ps.setInt(4,bSum);
            ps.setString(5,path);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

    //删除数据行
    public int delete(String bName){
        String sql="delete from books where bName=?";
        PreparedStatement ps=util.createPstmt(sql);
        int result=0;
        try {
            ps.setString(1,bName);
            result=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }

    //更新数据行
    public int update(String bName1,String bAut,String bPub,int bSum,String path,String bName2){
        String sql="update books set bName=?,bAut=?,bPub=?,bSum=?,path=? where bName=?";
        PreparedStatement ps=util.createPstmt(sql);
        int result=0;
        try {
            ps.setString(1,bName1);
            ps.setString(2,bAut);
            ps.setString(3,bPub);
            ps.setInt(4,bSum);
            ps.setString(5,path);
            ps.setString(6,bName2);
            result=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }
    //书的数量减1
    public int updateSum(String id){
        String sql="update books set bSum = bSum-1 where id=?";
        PreparedStatement ps=util.createPstmt(sql);
        int result=0;
        try {
            ps.setString(1,id);
            result=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;

    }

    //查询数据行
    //1.查询所有数据
    public List findAll(String userId){
        int result=0;
        String sql="select * from books order by id desc";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            rs=ps.executeQuery();
            //将临时表数据行转换为实体类对象保管，存在list中
            while (rs.next()){
                user_bookDao u_bDao=new user_bookDao();
                result=u_bDao.isBorrowed(userId,rs.getString("bName"));
                int id=rs.getInt("id");
                String bName=rs.getString("bName");
                String bAut=rs.getString("bAut");
                String bPub=rs.getString("bPub");
                int bSum=rs.getInt("bSum");
                String path=rs.getString("path");
                Book book=new Book(id,bName,bAut,bPub,bSum,path,result);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }
    //2.根据name关键字模糊查询
    public List select(String bName,String bType,String userId){
        int result=0;
        String sql="select * from books where "+bType+" like ? order by id desc";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            ps.setString(1,"%"+bName+"%");
            rs=ps.executeQuery();
            while (rs.next()){
                user_bookDao u_bDao=new user_bookDao();
                result=u_bDao.isBorrowed(userId,rs.getString("bName"));
                int id=rs.getInt("id");
                String bName2=rs.getString("bName");
                String bAut=rs.getString("bAut");
                String bPub=rs.getString("bPub");
                int bSum=rs.getInt("bSum");
                String path=rs.getString("path");
                Book book=new Book(id,bName2,bAut,bPub,bSum,path,result);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }
    //根据bid查询一条数据
    public Book selectId(String bid){
        String sql="select * from books where id=?";
        Book book=new Book();
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        try {
            ps.setString(1,bid);
            rs=ps.executeQuery();
            while (rs.next()){
                book.setbName(rs.getString("bName"));
                book.setId(rs.getInt("id"));
                book.setbAut(rs.getString("bAut"));
                book.setbPub(rs.getString("bPub"));
                book.setbSum(rs.getInt("bSum"));
                book.setPath(rs.getString("path"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return book;
    }
    //根据type查询
    public List selectType(String type,String userId){
        int result=0;
        String sql="select * from books where type=? order by id desc";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            ps.setString(1,type);
            rs=ps.executeQuery();
            while (rs.next()){
                user_bookDao u_bDao=new user_bookDao();
                result=u_bDao.isBorrowed(userId,rs.getString("bName"));
                int id=rs.getInt("id");
                String bName2=rs.getString("bName");
                String bAut=rs.getString("bAut");
                String bPub=rs.getString("bPub");
                int bSum=rs.getInt("bSum");
                String path=rs.getString("path");
                Book book=new Book(id,bName2,bAut,bPub,bSum,path,result);
                list.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }
    //根据bName查询一本书
    public Book findBook(String bName,String userId){
        int result=0;
        Book book=new Book();
        String sql="select * from books where bName=?";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            ps.setString(1,bName);
            rs=ps.executeQuery();
            while (rs.next()){
                user_bookDao u_bDao=new user_bookDao();
                result=u_bDao.isBorrowed(userId,rs.getString("bName"));
                int id=rs.getInt("id");
                String bName2=rs.getString("bName");
                String bAut=rs.getString("bAut");
                String bPub=rs.getString("bPub");
                int bSum=rs.getInt("bSum");
                String path=rs.getString("path");
                book=new Book(id,bName2,bAut,bPub,bSum,path,result);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return book;
    }


}
